/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.pedagogico;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Giane
 */
public class NivellDao {

    public NivellDao() {
    }

    public void inserirNivel(String idioma, String codigo, String nivel, String duracao, String qtdTurmas, String valor) throws SQLException {
        Connection conexao;
        String id_idioma = id_idioma(idioma);
       
        String select_ano = "INSERT INTO `bdeng2`.`nvl_nivel` (`nvl_id_idioma`, `nvl_codigo`, `nvl_nome`, `nvl_duracaol`, `nvl_qtd_turma`, `nvl_valor`) VALUES ('" + id_idioma + "', '"
                + codigo + "', '" + nivel + "', '" + duracao + "', '" + qtdTurmas + "','"+valor+"');";

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");


        Statement stmt2 = (Statement) conexao.createStatement();
        stmt2.execute(select_ano);
        conexao.close();


    }

    public String id_idioma(String idioma) throws SQLException {
        Connection conexao;
        String result = null;
        String select_idm = "select idm_id from idm_idioma where idm_descricao like '" + idioma + "';";
        System.out.println(select_idm);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idm);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }

    public ArrayList<String> consultaNivel() throws SQLException {
        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();
        String select_ano = "SELECT nvl_nome FROM nvl_nivel;";
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //Class.forName("com.mysql.jdbc.Driver");
        conexao = DriverManager.getConnection(url, "root", "dados");
        //System.out.println("2 - " + select_ano);

        PreparedStatement stmt = conexao.prepareStatement(select_ano);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            result.add(rs.getString(1));
            // System.out.println(rs.getString(1));
        }

        conexao.close();
        return result;

    }

    public ArrayList<String> consultaTurmaNivel(String nome_nivel) throws SQLException {
        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();
        String select_dados_turma = "SELECT idm_descricao, nvl_codigo ,nvl_nome,nvl_duracaol,nvl_qtd_turma FROM nvl_nivel,idm_idioma where nvl_id_idioma = idm_id and nvl_nome = '"+nome_nivel+"';";
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //Class.forName("com.mysql.jdbc.Driver");
        conexao = DriverManager.getConnection(url, "root", "dados");
        //System.out.println("2 - " + select_ano);

        PreparedStatement stmt = conexao.prepareStatement(select_dados_turma);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            for (int i = 1; i < 6; i++) {
                result.add(rs.getString(i));
            }
         }

        conexao.close();
        return result;

    }

    public String verificaNivelExiste(String codigo) throws SQLException {
        String select_count = "SELECT count(nvl_id) FROM nvl_nivel where nvl_codigo = " + codigo + " ;";

        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String verificarNivelMatricula(String nmNivel) throws SQLException {
        String select_count = null;

        select_count = "select count(alu_aluno_alu_matricula) from mat_matricula,nvl_nivel where mat_id_nivel = nvl_id and nvl_nome = '"+nmNivel+"';";
         System.out.print("SELECT "+select_count);

        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public void removeNivel(String nmNivel) throws SQLException {
         String select_remove = null;
         
         String id = id_Nivel(nmNivel);
         
         select_remove = "DELETE FROM nvl_nivel WHERE nvl_id = " + id + ";";
         System.out.print("DELETE"+select_remove);
        
        Connection conexao;

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_remove);

        stmt.execute();

        conexao.close();
    }
    
    public String id_Nivel(String nmNivel) throws SQLException {
    
        String result = null;
        String select_id = "select nvl_id from nvl_nivel  WHERE nvl_nome = '"+nmNivel+"';";
        

        
        Connection conexao;
        
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_id);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result; 
    }

    public String consultaValor(String nivel) throws SQLException {
        String result = null;
        String select_id = "select nvl_valor from nvl_nivel WHERE nvl_nome = '"+nivel+"';";
        

        
        Connection conexao;
        
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_id);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;
    }
    
    

}

